using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CommonLibrary.DataModel.WorkFlow;


namespace CommonLibrary.DB
{
    /// <summary>
    /// 类<c>DBExecuteUtil</c> 执行单条sql(无事务)/查询sql的工具类
    /// 
    /// <para>作者：陈涛</para>
    /// <para>更改记录：</para>
    /// <para>-------------------------------------------------------</para>
    /// <para>改动人           时间                      原因        </para>
    /// <para>-------------------------------------------------------</para>
    /// <para>陈涛          2009-05-15                 创建文件      </para>
    /// <para>陈涛          2009-07-20                 增加了分页方法</para>
    /// <para>陈涛          2009-07-31   对分页OrderField的检查，以及分页可返回交错数组或者DataTable </para>
    /// <para>陈涛          2009-09-17   对自动统计总行数的修改，group by的情况下也能正确统计 </para>
    /// </summary>
    public class DBExecuteUtil : DBUtil
    {

        /// <summary>
        /// 执行cud，无参数形式
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int executeSql(string sql)
        {
            return executeSql(sql, null);
        }

        /// <summary>
        /// 多参数形式
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static int executeSql(string sql, Hashtable hts)
        {
            return executeCud(sql, CommandType.Text, hts, null);
        }

        /// <summary>
        /// 通过设置参数SqlParameter进行查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParas"></param>
        /// <returns></returns>
        public static int executeSqlWithPar(string sql, SqlParameter[] sqlParas)
        {
            return executeCud(sql, CommandType.Text, null, sqlParas);
        }

        public static Int32 executeSqlWithPar(String sql, CommandType type, SqlParameter[] sqlParas)
        {
            return executeCud(sql, type, null, sqlParas);
        }

        /// <summary>
        /// 执行查询语句,返回交错数组
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string[][] querySqlArray(string sql)
        {
            return convertTableToArray(querySqlTable(sql));
        }

        public static string[][] querySqlArray(string sql, Hashtable args)
        {
            return convertTableToArray(querySqlTable(sql, args));
        }

        /// <summary>
        /// 无结果返回 null
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParas"></param>
        /// <returns></returns>
        public static string[][] querySqlArray(string sql, SqlParameter[] sqlParas)
        {
            return convertTableToArray(querySqlTable(sql, sqlParas));
        }

        public static string[][] querySqlArray(DataTable dt)
        {
            return convertTableToArray(dt);
        }

        /// <summary>
        /// 执行查询语句,返回dataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable querySqlTable(string sql)
        {
            return queryTable(sql, CommandType.Text, null, null);
        }

        public static DataTable querySqlTable(string sql, Hashtable args)
        {
            DataTable dt = queryTable(sql, CommandType.Text, args, null);
            //return queryTable(sql, CommandType.Text, args,null);
            return dt;
        }

        public static DataTable querySqlTable(string sql, SqlParameter[] sqlParas)
        {
            DataTable dt = queryTable(sql, CommandType.Text, null, sqlParas);
            return dt;
        }

        /// <summary>
        /// 获得唯一的记录
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string getUniqueResult(string sql)
        {
            return getUniqueResult(sql, null, null);
        }

        /// <summary>
        /// 获得唯一的记录
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static string getUniqueResult(string sql, Hashtable args)
        {
            return getUniqueResult(sql, args, null);
        }

        public static string getUniqueResult(string sql, SqlParameter[] paras)
        {
            return getUniqueResult(sql, null, paras);
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static bool Exists(string strSql)
        {
            return Exists(strSql, null);
        }

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static bool Exists(string strSql, SqlParameter[] paras)
        {
            string obj = getUniqueResult(strSql, null, paras);
            int cmdresult;
            if (Object.Equals(obj, string.Empty))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj);
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pageObject"></param>
        public static void findDataByPage(string sql, PageObject pageObject)
        {
            findDataByPage(sql, new Hashtable(), pageObject);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="hst"></param>
        /// <param name="pageObject"></param>
        public static void findDataByPage(string sql, Hashtable hst, PageObject pageObject)
        {
            sql = sql.ToUpper();
            if (pageObject.AutoCount)
            {
                getTotalCount(sql, hst, null, pageObject);
            }

            if (!pageObject.isFirstSetted())
            {
                if (pageObject.IsResultTable)
                    pageObject.TableData = querySqlTable(sql, hst);
                else
                    pageObject.ArrayData = querySqlArray(sql, hst);
                return;
            }
            StringBuilder sb = new StringBuilder("select * from (");
            sb.Append(sqlAddRowNumber(sql, pageObject));
            sb.Append(") _myResult where RowNumber>")
                .Append(pageObject.getFirst()).Append(" and RowNumber<").Append(pageObject.getLast());
            if (pageObject.IsResultTable)
                pageObject.TableData = querySqlTable(sb.ToString(), hst);
            else
                pageObject.ArrayData = querySqlArray(sb.ToString(), hst);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParas"></param>
        /// <param name="pageObject"></param>
        public static void findDataByPage(string sql, SqlParameter[] sqlParas, PageObject pageObject)
        {
            sql = sql.ToUpper();
            if (pageObject.AutoCount)
            {
                getTotalCount(sql, null, sqlParas, pageObject);
            }

            if (!pageObject.isFirstSetted())
            {
                if (pageObject.IsResultTable)
                    pageObject.TableData = querySqlTable(sql, sqlParas);
                else
                    pageObject.ArrayData = querySqlArray(sql, sqlParas);
                return;
            }
            StringBuilder sb = new StringBuilder("select * from (");
            sb.Append(sqlAddRowNumber(sql, pageObject));
            sb.Append(") _myResult where RowNumber>")
                .Append(pageObject.getFirst()).Append(" and RowNumber<").Append(pageObject.getLast());
            if (pageObject.IsResultTable)
                pageObject.TableData = querySqlTable(sb.ToString(), sqlParas);
            else
                pageObject.ArrayData = querySqlArray(sb.ToString(), sqlParas);
        }

        /// <summary>
        /// 生成增加RowNumber的sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pageObject"></param>
        /// <returns></returns>
        private static StringBuilder sqlAddRowNumber(string sql, PageObject pageObject)
        {
            int fromIndex = sql.IndexOf("FROM");
            if (fromIndex == 0)
            {
                return new StringBuilder();
            }
            if (pageObject.OrderField == null || pageObject.OrderField == string.Empty)
            {
                throw new ArgumentException("必须设置Page对象的OrderField(排序)属性");
            }
            StringBuilder sqlBuilder = new StringBuilder(sql.Substring(0, fromIndex));

            sqlBuilder.Append(",ROW_NUMBER() OVER (ORDER BY ").Append(pageObject.OrderField)
            .Append(") AS RowNumber ");

            sqlBuilder.Append(sql.Substring(fromIndex, sql.Length - fromIndex));

            return sqlBuilder;
        }

        /// <summary>
        /// 获得总数目
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="pageObject"></param>
        private static void getTotalCount(string sql, Hashtable hst,
            SqlParameter[] sqlParas, PageObject pageObject)
        {
            int fromIndex = sql.IndexOf("FROM");
            if (fromIndex == 0)
            {
                pageObject.TotalCount = 0;
                return;
            }
            StringBuilder sb = new StringBuilder();
            if (pageObject.GroupFlag)       //如果含有Group by语句
            {
                sb.Append("SELECT COUNT(").Append(pageObject.CountField).Append(") from (")
                .Append(sql).Append(") COUNT_TABLE");
            }
            else
            {
                sb.Append("SELECT COUNT(").Append(pageObject.CountField).Append(")")
                .Append(sql.Substring(fromIndex, sql.Length - fromIndex));
            }

            string result = getUniqueResult(sb.ToString(), hst, sqlParas).ToString();
            if (result != string.Empty) pageObject.TotalCount = Convert.ToInt64(result);
            else pageObject.TotalCount = 0;
        }

        /// <summary>
        ///  获得数据泛型数组
        /// </summary>
        /// <param name="sql"></param>
        public static System.Collections.Generic.List<String> queryDataList(String sql)
        {
            return getDataList(sql, null);
        }

        public static System.Collections.Generic.List<String> queryDataList(String sql, SqlParameter[] paras)
        {
            return getDataList(sql, paras);
        }
    }

}
